/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cashmate.hibernate.dao;

import com.cashmate.hibernate.pojo.Dccr;
import com.cashmate.hibernate.pojo.Performance;
import com.cashmate.hibernate.pojo.Summary;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.exception.LockAcquisitionException;
import org.hibernate.transform.Transformers;

/**
 *
 * @author Dean
 */
public class DccrDAO extends DAO {

    public DccrDAO() {
        super();
    }

    @SuppressWarnings("unchecked")
    public List<Dccr> getAll() throws Exception {
        List<Dccr> a = (List<Dccr>) getSession().createQuery("from Creditmatrix a").setCacheable(true).list();
        flush();
        return a;
    }

    @SuppressWarnings("unchecked")
    public Dccr get(int id) throws Exception {
        Dccr a = (Dccr) getSession().createQuery("from Dccr a where a.dccrid=:id").setParameter("id", id).setCacheable(true).uniqueResult();
        flush();
        return a;
    }

    @SuppressWarnings("unchecked")
    public void update(Dccr a) throws Exception {
        try {
            begin();
            getSession().update(a);
            commit();
            flush();
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }

    @SuppressWarnings("unchecked")
    public void updateGroup(Dccr a) throws Exception {
        if (!getSession().getTransaction().isActive()) {
            begin();
        }
        getSession().update(a);
    }

    @SuppressWarnings("unchecked")
    public void merge(Dccr a) throws Exception {
        if (!getSession().getTransaction().isActive()) {
            begin();
        }
        getSession().merge(a);
    }

    public void commitGroup() throws Exception {
        try {
            if (getSession().getTransaction().isActive()) {
                commit();
            }
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }

    @SuppressWarnings("unchecked")
    public void insert(Dccr a) throws Exception {
        try {
            begin();
            getSession().save(a);
            commit();
            flush();
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }

    @SuppressWarnings("unchecked")
    public void delete(int id) throws Exception {
        try {
            begin();
            getSession().delete(get(id));
            commit();
            flush();
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }

    @SuppressWarnings("unchecked")
    public void deleteGroup(int id) throws Exception {
        getSession().delete(get(id));
    }

    @SuppressWarnings("unchecked")
    public List<Dccr> findByCriteria(DetachedCriteria dc, int from, int size) throws Exception {
        Criteria criteria = dc.getExecutableCriteria(getSession());
        criteria.setFirstResult(from);
        criteria.setMaxResults(size);
        criteria.setCacheable(true);
        List<Dccr> list = criteria.list();
        flush();
        return list;
    }

    @SuppressWarnings("unchecked")
    public List<Dccr> findByCriteria(DetachedCriteria dc) throws Exception {
        Criteria criteria = dc.getExecutableCriteria(getSession());
        criteria.setCacheable(true);
        List<Dccr> list = criteria.list();
        flush();
        return list;
    }

    @SuppressWarnings("unchecked")
    public List findProjectedCriteria(DetachedCriteria dc) throws Exception {
        Criteria criteria = dc.getExecutableCriteria(getSession());
        criteria.setCacheable(true);
        flush();
        return criteria.list();
    }
    
    @SuppressWarnings("unchecked")
    public Object findSingleProjectedCriteria(DetachedCriteria dc) throws Exception{
        Criteria criteria = dc.getExecutableCriteria(getSession());
        criteria.setMaxResults(1);
        return criteria.uniqueResult();
    }

    @SuppressWarnings("unchecked")
    public int countByCriteria(DetachedCriteria dc) throws Exception {
        Criteria criteria = dc.getExecutableCriteria(getSession());
        criteria.setCacheable(true);
        criteria.setProjection(Projections.rowCount());
        int count = ((Integer) criteria.list().get(0)).intValue();
        flush();
        return count;
    }

    //OTHER METHODS...
    //Call to MySQL Stored Procedure.
    @SuppressWarnings("unchecked")
    public void dccrProcedure(String date, int branchid) throws Exception {
        try {
            begin();
            Query query = getSession().createSQLQuery("CALL dccrProcedure(?,?)");
            query.setString(0, date);
            query.setInteger(1, branchid);
            query.executeUpdate();
            commit();
            flush();
        } catch (LockAcquisitionException lae){
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }
    
    @SuppressWarnings("unchecked")
    public void updateDccr(String date, int branchid) throws Exception {
        try {
            begin();
            Query query = getSession().createSQLQuery("CALL updateDccr(?,?)");
            query.setString(0, date);
            query.setInteger(1, branchid);
            query.executeUpdate();
            commit();
            flush();
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }

    @SuppressWarnings("unchecked")
    public List<Summary> weekPerformanceProcedure(String date, int branchid, String areacode) throws Exception {
        Query query = getSession().getNamedQuery("weekPerformanceProcedure").setResultTransformer(Transformers.aliasToBean(Summary.class));
        query.setString(0, date);
        query.setInteger(1, branchid);
        if (areacode == null) {
            query.setParameter(2, null);
        } else if (areacode.length() < 1) {
            query.setParameter(2, null);
        } else {
            query.setParameter(2, areacode);
        }
        query.setCacheable(true);
        flush();
        return query.list();
    }

    @SuppressWarnings("unchecked")
    public List<Performance> monthlyPerformanceProcedure(int month, int year, int branchid, String areacode) throws Exception {
        Query query = getSession().getNamedQuery("monthlyPerformanceProcedure").setResultTransformer(Transformers.aliasToBean(Performance.class));
        query.setInteger(0, month);
        query.setInteger(1, year);
        query.setInteger(2, branchid);

        if (areacode == null) {
            query.setParameter(3, null);
        } else if (areacode.length() < 1) {
            query.setParameter(3, null);
        } else {
            query.setParameter(3, areacode);
        }
        query.setCacheable(true);
        flush();
        return query.list();
    }

    @SuppressWarnings("unchecked")
    public Dccr getDccr(String date, int branchid, int dccrid) throws Exception {
        Dccr d = (Dccr) getSession().createQuery("from Dccr d where d.dategenerated=:d AND d.branchid=:b AND d.dccrid=:dc").setParameter("d", date).setParameter("b", branchid).setParameter("dc", dccrid).setCacheable(true).uniqueResult();
        flush();
        return d;
    }

    @SuppressWarnings("unchecked")
    public void updateDccr(int dccrid, double debit) throws Exception {
        try {
            Query q = getSession().createSQLQuery("CALL updateDccr(?,?)");
            q.setInteger(0, dccrid);
            q.setDouble(1, debit);
            q.executeUpdate();
            commit();
            flush();
        } catch (Exception e) {
            rollback();
            throw e;
        }
    }
}
